package manage;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class InitDatabase {

    private DBManager dbManager;
    private Connection conn;
    private Statement statement;

    public static void main(String[] args) {
        InitDatabase initDatabase = new InitDatabase();
        initDatabase.initDatabase();
    }

    public void connectToDB() {
        dbManager = new DBManager();
        conn = dbManager.getConnection();
        try {
            this.statement = conn.createStatement();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void closeDB() {
        dbManager.closeConnections();
    }

    public void initDatabase() {
        initHotelTable();
        initFlightTable();
        initUserTable();
        initTravelGuideTable();
        initOrderTable();
        System.out.println("Ticket database has been initialized.");
    }

    public void initHotelTable() {
        connectToDB();
        checkExistedTable("HOTEL_T");
        dbManager.updateDB("create table HOTEL_T(name varchar(50),imgUrl varchar(200),address varchar(200), city varchar(200), price double,description varchar(200),rating int,room int)");
        String query = "insert into HOTEL_T values('Paton View Hotel','PATON','14F, Section A, Hangzhou Yin, No.2030 Jianghui Road, Binjiang District','Hangzhou',229,'The Paton View Hotel (Star avenue riverside store) provides a great place for travelers to relax after a busy day.',3,4)," + "('Merchant Marco Hotel','MERCHANT','No.38 Pinghai Road, Shangcheng District','Hangzhou',477,'The Merchant Marco Hotel is an ideal spot for travelers wanting to discover the city.',4,2)," + "('Qiyupai Hotel','QIYUPAI','No.171 Zhongshan Middle Road, Shangcheng District','Hangzhou',340,'The Qiyupai Hotel (Hangzhou Xihu Hefang Street Branch) is one of the newest hotels in Hangzhou, having opened in 2022.',3,5)," + "('UrCove by HYATT ','URCOVE','No. 346 Yan an Road, Gongshu District','Hangzhou',614,'The hotel is located by the beautiful West Lake, adjacent to the World Cultural Landscape Heritage, and across the street from Kerry Center',4,1)," + "('Jiuqi Apartment','JIUQI','Room A525, Building A, Dongfang Jinzuo, No.1 Yuewang Road, Shangcheng District','Hangzhou',191,'The Jiuqi | Hangzhou Yousu Holiday Apartment is one of the newest hotels in Hangzhou, having been renovated in 2021.',2,9)," + "('Captain No. 5 Youth Hotel','CAPTION','Minhang District','Shanghai',247,'Choosing the Shanghai Captain 5 Youth Hotel is a journey of exploration, connection, and freedom.',2,11)," + "('Tingzhou Hotel','TINGZHOU','787 Qinzhou Road, Xuhui District','Shanghai',370,'Tingzhou Hotel is committed to building a warm and trustworthy hotel brand, carrying the caring mission of enabling every employee to realize their self-worth.',3,7)," + "('Holiday Inn Shanghai Hongqiao West','HOLIDAY','No.2000 Huqingping Road, Qingpu District','Shanghai',562,'The hotel offers its guests regular complimentary shuttle bus to&from East Xujing Station Metro Line 2 as well as NECC',4,1)," + "('Kingtown Hotel Plaza Shanghai','KINGTOWN','No.126 Xinzha Road, Huangpu District','Shanghai',530,'Rooms and suites in this Shanghai hotel are all outfitted with LCD PC TVs and a Wi-Fi network covers the whole hotel.',4,2)," + "('baseLITE TONGFENG Serviced Apartment','BASELITE','No.699 TONGFENG ROAD, HONGKOU DISTRICT','Shanghai',425,'Opened in 2021, the baseLITE TONGFENG Serviced Apartment is a great accommodation choice in Shanghai.',2,14)," + "('Beijing Cheap Fang Hotel','CHEAPFANG','No.65-77 Xianyukou Street, Dongcheng District','Beijing',728,'The Beijing Cheap Fang Hotel (Tiananmen Square) is one of the newest hotels in Beijing, having opened in 2021.',4,6)," + "('UrCove by HYATT Beijing Forbidden City','URCOVE','Tian an Building A, Hualong Street, Nan Heyan Street, Dongcheng District','Beijing',874,'The UrCove by HYATT Beijing Forbidden City is a great choice for guests looking for accommodation in Beijing, having been recently renovated in 2022.',4,4)," + "('Grand Bay Hotel Beijing','GRAND','No.36 Mixi Road, Miyun District','Beijing',1611,'The hotel is located on Mixi Road in Miyun District. Surrounding tourist attractions such as Taoyuan Xiangu, Heilongtan, and Nanshan Ski Resort can be reached by car in about 20 minutes.',5,4)," + "('Beijing Xianglun She Hotel ','XIANGLUN','No. 63, Liangshidian Street, Xicheng District','Beijing',463,'The Beijing Xianglun She Hotel provides a great place for travelers to relax after a busy day. ',2,5)," + "('Yitingzhenshe Hotel','YITINGZHENSHE','No.26 Buxiangzi Hutong','Beijing',348,'The Yitingzhenshe Hotel (Beijing Qianmen) provides a great place for travelers to relax after a busy day.',4,1)," + "('The Hidden House','THEHIDDEN','14F, Building C, Chengdu International Business Building, No.1 Tianxianqiao South Road','Chengdu',332,'The Hidden House(Chengdu Taikoo Li & Hejiangting)) is an ideal spot for travelers wanting to discover the city.',4,6)," + "('Howdy Smart Hotel ','HOWDY','No.88 Section 4 of Hongxing Road','Chengdu',198,'Howdy SMART Hotel (Chunxi branch) is located in the center of Chengdu CBD area and district Chunxi gold zone',3,4)," + "('Chengdu Hexi Apartment','ChengduHEXI','219 Tongbao Street, Global Huiweiran','Chengdu',198,'The Hexi hotel was recently opened in 2021, making it a fantastic choice for those staying in Chengdu.',2,11)," + "('Hilton Chengdu Chenghua','HILTON','No. 99 West Tashuiqiao Street','Chengdu',471,'At Hilton Chengdu Chenghua, it could not be easier to get around.',5,7)," + "('Yinxitang Hotel','YINXITANG','No. 5 Jiangjun Street','Chengdu',479,'The hotel is located in a feng shui treasure trove in the city center, nestled deep in the old street',4,2)," + "('Guangzhou Atour Hotel','GuangzhouATOUR','No.168, Si You Xin Road','Guangzhou',500,'The hotel is located at the intersection of Wuyang New Town and Zhujiang New Town CBD business district',4,8)," + "('Mercure Guangzhou','MERCURE','No. 182 Beijing Road','Guangzhou',661,'Mercure Guangzhou Beijing Road Pedestrian Street Hotel is a mid-up hotel jointly managed by Accor Group and H World Group',4,3)," + "('Hotel Guangzhou Tower Haizhu Wanda Plaza','HOTELGuangzhouTOWER','No.266 Yijing Road','Guangzhou',265,'The Hotel Guangzhou Tower Haizhu Wanda Plaza was recently opened in 2022, making it a fantastic choice for those staying in Guangzhou.',2,9)," + "('Hongyang Liyue Hotel','HONGYANG','485 Xingang Middle Road','Guangzhou',319,'The hotel is located at No. 485 Xingang Middle Road, adjacent to the Pazhou International Convention and Exhibition Center',4,11)," + "('Shiyi City Zhan','SHIYI','1st Floor, Zhujiang Xin an Apartment, No. 28 Yuhan Road','Guangzhou',369,'The Shiyi City Zhan (Guangzhou Tower) was recently opened in 2021, making it a fantastic choice for those staying in Guangzhou.',2,7)";
        dbManager.updateDB(query);
        closeDB();
    }

    public void initFlightTable() {
        connectToDB();
        checkExistedTable("FLIGHT_T");
        dbManager.updateDB("create table FLIGHT_T(name varchar(50),price double,departure varchar(50),destination varchar(50),departureTime time,arriveTime time,seat int)");
        String query = "insert into FLIGHT_T values('JL05',887,'Hangzhou','Guangzhou','20:20:00','23:34:00',105)," + "('LG43',604,'Hangzhou','Guangzhou','15:23:00','18:03:00',113)," + "('HD73',701,'Hangzhou','Guangzhou','9:31:00','12:40:00',98)," + "('JL24',1035,'Hangzhou','Chengdu','14:06:00','17:10:00',120)," + "('LG42',876,'Hangzhou','Chengdu','10:12:00','13:12:00',96)," + "('HD37',971,'Hangzhou','Chengdu','20:08:00','23:20:00',105)," + "('JL52',1288,'Chengdu','Shanghai','9:10:00','12:31:00',108)," + "('LG25',998,'Chengdu','Shanghai','13:35:00','16:48:00',113)," + "('HD72',1088,'Chengdu','Shanghai','17:14:00','20:41:00',98)," + "('JL35',731,'Beijing','Shanghai','11:25:00','14:21:00',98)," + "('LG55',552,'Beijing','Shanghai','16:31:00','19:40:00',102)," + "('HD81',669,'Beijing','Shanghai','15:51:00','18:37:00',105)," + "('JL12',893,'Shanghai','Guangzhou','18:24:00','20:35:00',115)," + "('LG15',723,'Shanghai','Guangzhou','20:33:00','23:42:00',103)," + "('HD92',901,'Shanghai','Guangzhou','14:51:00','17:32:00',108)," + "('JL89',989,'Beijing','Chengdu','12:53:00','15:42:00',112)," + "('LG45',783,'Beijing','Chengdu','16:35:00','19:26:00',106)," + "('HD28',802,'Beijing','Chengdu','20:42:00','23:37:00',108)";
        dbManager.updateDB(query);
        closeDB();
    }

    public void initUserTable() {
        connectToDB();
        checkExistedTable("USER_T");
        dbManager.updateDB("create table USER_T(username varchar(20) NOT NULL UNIQUE, password varchar(20))");
        String query = "insert into USER_T values('para05','123456')";
        dbManager.updateDB(query);
        closeDB();
    }

    public void initTravelGuideTable() {
        connectToDB();
        checkExistedTable("TRAVEL_GUIDE_T");
        dbManager.updateDB("create table TRAVEL_GUIDE_T(id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title varchar(100),content varchar(5000),imgUrl varchar(100),likeCount int)");
        String query = "insert into TRAVEL_GUIDE_T(title,content,imgUrl,likeCount) values('A Stroll Through Imperial Secrets: Forbidden City Travel Guide','<p>The Forbidden City, also known as the Palace Museum, stands as one of Beijing''s most iconic landmarks and is an essential destination for anyone interested in exploring Chinese history and culture. Situated in the heart of Beijing, it once served as the imperial palace for 24 emperors during the Ming and Qing dynasties. Today, it is a UNESCO World Heritage site, attracting millions of visitors each year.</p>  <p>Start your visit at the southern Meridian Gate, the main entrance to the Forbidden City. Here, you can rent an audio guide or opt for a guided tour to gain a deeper understanding of the rich history and architectural significance of this vast palace complex. As you step through the gates, prepare to be awed by the Hall of Supreme Harmony, the largest wooden structure in China, which was traditionally used for imperial ceremonies and is one of the most impressive features of the palace.</p>  <p>As you continue your exploration, make your way to the Palace of Heavenly Purity, the private living quarters of the emperors. The serene atmosphere of this area, along with its intricate design, gives you a glimpse into the opulent lifestyle of China''s imperial family. Don''t forget to visit the Imperial Garden, where you can stroll among ancient pines, beautiful rock formations, and traditional pavilions, creating a perfect escape from the crowds.</p>  <p>It''s recommended to spend at least half a day here, as the Forbidden City spans nearly 980 buildings across its enormous grounds. To avoid the busiest times, try to visit on weekdays, as weekends can be especially crowded. Be sure to purchase your tickets in advance, as the daily visitor numbers are capped to preserve the site''s integrity.</p>  <p>To complete your visit, head to Jingshan Park, located just north of the Forbidden City. A short hike up the hill will reward you with a stunning panoramic view of the entire palace grounds and the surrounding Beijing skyline. This vantage point provides a perfect conclusion to your exploration of this extraordinary site.</p>  <p>With its rich history, stunning architecture, and cultural significance, the Forbidden City offers an unforgettable journey into China''s imperial past, making it a must-see for anyone visiting Beijing.</p>','gugong',2438)," + "('Touching the Skies: A Guide to the Oriental Pearl Tower','<p>The Oriental Pearl Tower, located in Shanghai''s Pudong District, is a must-see landmark known for its futuristic design and panoramic city views. Standing at 468 meters, it offers several observation decks, including the thrilling 259-meter glass walkway and the 351-meter \"Space Module,\" both providing breathtaking perspectives of Shanghai''s skyline.Getting there is convenient via Metro Line 2 to Lujiazui Station, with just a short walk to the tower. It''s open daily, typically from 8:00 AM to 9:30 PM, though hours may vary on holidays. Tickets come in different packages depending on which decks you want to visit, so booking in advance is recommended.Dining at the 267-meter revolving restaurant is a highlight, offering a mix of Chinese and Western cuisine with views that gradually rotate to showcase the entire cityscape.</p><p>The nearby Lujiazui area also has excellent dining options and attractions like the Shanghai Ocean Aquarium and Riverside Promenade, ideal for a post-visit stroll.For the best experience, visit in the late afternoon to catch both daylight and Shanghai''s mesmerizing night lights. The Oriental Pearl Tower combines sightseeing, dining, and a touch of history with its on-site museum, making it a perfect destination for any visitor to Shanghai.</p>','shanghai',5586)," + "('The Enchanted Waters of Jiuzhaigou','<p>Jiuzhaigou Valley, located in Sichuan Province, is a UNESCO World Heritage Site renowned for its breathtaking natural beauty, clear lakes, and lush forests. This stunning national park is perfect for travelers looking to experience unique landscapes and traditional Tibetan culture.The best time to visit Jiuzhaigou is in autumn (September to November) when the foliage turns vibrant red, orange, and yellow, creating beautiful reflections in the park''s clear lakes. Spring and early summer are also excellent for mild weather and blooming flowers, while winter offers a quieter, snowy landscape. However, it''s best to avoid visiting during China''s National Day holiday in early October, as the park can be very crowded.</p><p>Jiuzhaigou is accessible by air, with flights to Jiuzhai Huanglong Airport from major cities like Chengdu, Chongqing, and Xi''an. From the airport, it''s about a 90-minute drive to the park. Public buses and private shuttles are available, or you can opt to stay in a nearby hotel in Jiuzhaigou Town for easy access.Inside Jiuzhaigou, the main attractions are the park''s turquoise lakes, waterfalls, and dense forests. Highlights include Five Flower Lake, known for its colorful waters, and Nuorilang Waterfall, one of the largest waterfalls in China. There are also beautiful hiking trails and lookout points throughout the valley. The park operates eco-friendly shuttle buses, making it easy to get around between the scenic spots.Jiuzhaigou also offers a glimpse into Tibetan culture, with traditional villages where visitors can experience local customs and cuisine. Tibetan barley wine, yak butter tea, and Tibetan hotpot are local specialties worth trying.To make the most of your trip, plan for at least a full day in the park, and consider visiting nearby Huanglong Scenic Area, famous for its terraced pools and limestone formations. Be sure to wear comfortable walking shoes and bring a camera to capture Jiuzhaigou''s enchanting beauty.</p>','jiuzhaigou',2970)," + "('Reaching New Heights: A Guide to the Canton Tower','<p>Canton Tower, an architectural marvel along the Pearl River in Guangzhou, offers an unforgettable experience blending modern elegance and panoramic views. Start your visit in the late afternoon, when daylight offers clear views of the cityscape. Take the elevator up to the observation deck, where you can step onto the Skywalk, a glass bridge suspended high above the city, giving you a thrilling, bird''s-eye view.</p><p> For the adventurous, try the Bubble Tram, an open-air, transparent capsule that circles the top of the tower, or the Sky Drop, a heart-racing free-fall ride from the tower''s peak.At sunset, the view transforms as the tower and surrounding city begin to glow with vibrant lights. The Rainbow Plaza on the lower floors offers colorful LED displays and photo spots, capturing the tower''s lively energy. For dining, visit the rotating restaurant, where you can enjoy Cantonese dishes while the skyline slowly rotates around you. After dark, take a stroll along the Pearl River to admire the full, illuminated view of the tower and Guangzhou''s skyline. Canton Tower isn''t just a sight; it''s an experience of Guangzhou''s fusion of tradition and innovation, offering a view of the city that''s both breathtaking and memorable.</p>','canton',8394)," + "('Serenity Among the Islands','<p>Qiandao Lake, also known as Thousand Island Lake, is a picturesque lake located in Zhejiang Province, China. Known for its clear waters and 1,078 islands of varying sizes, this lake is an ideal getaway for nature lovers and those looking to unwind in a serene setting.The best time to visit Qiandao Lake is from April to October, especially in spring and autumn when the weather is mild and the scenery is most vibrant. Summer is also popular, offering a refreshing escape from the heat. The lake can be reached by a three-hour drive or high-speed train from Hangzhou, with buses and shuttles available to nearby hotels and scenic spots.Popular activities at Qiandao Lake include boat tours, where visitors can explore the islands up close. Among these, Meifeng Island offers some of the best panoramic views of the lake, while Monkey Island and Bird Island offer unique encounters with local wildlife. </p><p>For history enthusiasts, Longshan Island features ancient temples and artifacts, providing insight into the region''s cultural heritage.Cycling along the lakeside or hiking nearby trails are also popular ways to enjoy the natural beauty of the area. Visitors looking for a leisurely experience can try local fish dishes, as Qiandao Lake is known for its freshwater fish, prepared in traditional Zhejiang styles.For a full experience, consider staying overnight in a lakeside hotel to enjoy the sunrise over the islands. Whether you''re interested in scenic relaxation, outdoor activities, or local cuisine, Qiandao Lake offers a tranquil escape with beautiful landscapes and a range of activities.</p>','lake',5443)," + "('A Journey to Tranquility: Lingyin Temple Guide','<p>Lingyin Temple, located in Hangzhou, China, is one of the country''s oldest and most famous Buddhist temples, founded over 1,600 years ago. Nestled amidst lush mountains and surrounded by forests, Lingyin Temple, or the \"Temple of the Soul''s Retreat\" offers a serene and spiritual experience for visitors interested in history, architecture, and peaceful natural settings.The best time to visit Lingyin Temple is during the spring and autumn months, when the weather is mild, and the surrounding scenery is most beautiful. The temple is easily accessible from downtown Hangzhou; a short taxi or bus ride will bring you to the temple grounds, located within the larger Feilai Peak Scenic Area. Keep in mind that there is an entry fee for both the scenic area and the temple itself.Upon entering, visitors are greeted by magnificent stone carvings and statues on Feilai Peak, an area known for its intricate Buddhist rock carvings that date back to the Song and Yuan dynasties. </p><p>Within Lingyin Temple, highlights include the Hall of the Heavenly Kings, where visitors can see a massive Buddha statue, and the Grand Hall of the Great Sage, featuring an impressive 19-meter-tall statue of the Sakyamuni Buddha, one of the largest wooden Buddha statues in China.Lingyin Temple is an active monastery, and visitors are encouraged to respect the space by maintaining a quiet atmosphere and refraining from photography inside the main halls.</p><p> You''ll find monks practicing daily rituals, as well as incense offerings from locals and visitors alike.If you have extra time, explore the scenic trails around the temple, which offer peaceful walks through bamboo forests and mountain views. Nearby, you can visit Yongfu Temple and Taoguang Temple for a more secluded experience. Be sure to try local Hangzhou tea or vegetarian dishes in the nearby tea houses or temple restaurant, as Lingyin Temple has a long tradition of serving delicious vegetarian food.A visit to Lingyin Temple offers not only a journey through Buddhist history and culture but also a calming retreat from the city. For a memorable experience, visit in the early morning to enjoy the temple''s quiet beauty and capture the spirit of this ancient site.</p>','lingyinsi',6082)";
        dbManager.updateDB(query);
        closeDB();
    }

    public void initOrderTable() {
        connectToDB();
        checkExistedTable("ORDER_T");
        dbManager.updateDB("create table ORDER_T(id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, username varchar(20) NOT NULL, orderDate date, orderType varchar(20), orderName varchar(50), number int)");
        closeDB();
    }

    public void checkExistedTable(String name) {
        try {
            DatabaseMetaData dbmd = this.conn.getMetaData();
            String[] types = {"TABLE"};
            statement = this.conn.createStatement();
            ResultSet rs = dbmd.getTables(null, null, null, types);

            while (rs.next()) {
                String table_name = rs.getString("TABLE_NAME");
                if (table_name.equalsIgnoreCase(name)) {
                    statement.executeUpdate("Drop table " + name);
                    System.out.println("Table " + name + " has been deleted.");
                    break;
                }
            }
            rs.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}
